﻿using ClosedXML.Excel;
using ColorMapSystem.Helper;
using ColorMapSystem.Model;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ColorMapSystem.ColorMap
{
    public class SJHColorMap
    {
        public static void Run()
        {
            
            var sw = System.Diagnostics.Stopwatch.StartNew();

            try
            {
                string sql = @$"WITH lotinfo AS (
                        SELECT lot_id,
                               cust_code,
                               device,
					                     die_id,
                               wafer_lot
                        FROM htmms_get_lotinfo
                        WHERE cust_code = 'SJH'
                          AND device   = 'EJ002_C_CP'   -- 如果 device 可能为 NULL，下面第 3 点再处理
                    )
                    SELECT
                        main.*,
                        NVL(b.device, a.product_no) AS device,
                        b.cust_code,
                        b.die_id,
                        NVL(b.wafer_lot, a.wafer_lot_no) AS wafer_lot
                    FROM (
                        SELECT
                            t.lotid,
                            t.cp,
                            t.rp,
                            COUNT(1) AS count,
                            LISTAGG(t.wfno, ',') WITHIN GROUP (ORDER BY TO_NUMBER(t.wfno)) AS allwfno
                        FROM wip_lot_testerstate t
                             JOIN lotinfo b
                               ON t.lotid = b.lot_id    
                        WHERE t.colormapcompleted = 0
                          AND t.step >= 3
                        GROUP BY t.lotid, t.cp, t.rp
                    ) main
                    JOIN lotinfo b
                      ON main.lotid = b.lot_id
                    LEFT JOIN wip_lot a
                      ON a.order_no || '.' || a.order_sub_lot = b.lot_id";

                DataTable dt = OracleHelper.ExecuteQuery(sql);
                int total = dt.Rows.Count;

                LogHelper.Info($"共查询到{total}条SJH 的记录需要处理。");

                var records = new List<RecordInfo>(total);
                foreach (DataRow row in dt.Rows)
                {
                    records.Add(new RecordInfo
                    {
                        LotId   = row["LOTID"].ToString()!,
                        CpNo    = row["CP"].ToString()!,
                        RpNo    = row["RP"].ToString()!,
                        Count   = Convert.ToInt32(row["COUNT"]),
                        AllwfNo = row["ALLWFNO"].ToString()!,
                        WfLot   = row["WAFER_LOT"].ToString()!,
                        Device  = row["DEVICE"].ToString()!,
                        DieId   = row["DIE_ID"].ToString()!,
                        Cust    = row["CUST_CODE"].ToString()!
                    });
                }

                // 并发处理
                var options = new ParallelOptions
                {
                    MaxDegreeOfParallelism = Environment.ProcessorCount
                };

                Parallel.ForEach(records, options, record =>
                {
                    try
                    {
                        LogHelper.Info($"正在处理 LOT：{record.LotId} (WfLot: {record.WfLot})");
                        HandleOneLot(record);
                        LogHelper.Info($"成功处理 LOT：{record.LotId}");
                    }
                    catch (Exception ex)
                    {
                        LogHelper.Error($"处理 {record.LotId} 出错: {ex.Message}");
                    }
                });
            }
            finally
            {
                sw.Stop();
                LogHelper.Info($"程序运行结束，总耗时：{sw.Elapsed.TotalSeconds:F2} 秒 ({sw.Elapsed})");
            }

            



        }


        private static void HandleOneLot(RecordInfo record)
        {
            string templatePath = Path.Combine(Directory.GetCurrentDirectory(), "Template", "SJH-sample.xlsx");

            string tmbFileBasePath = ConfigurationManager.AppSettings["TmbFileBasePath"]!;
            string summaryBasePath = ConfigurationManager.AppSettings["TestdataPath"]!;

            if (!File.Exists(templatePath))
            {
                LogHelper.Error("模板文件不存在");
                return;
            }

            string custCode = record.Cust;
            string device = record.Device;
            string lotPath = Path.Combine(summaryBasePath, custCode, device, record.WfLot);

            if (!Directory.Exists(lotPath)) Directory.CreateDirectory(lotPath);

            string tempPath = Path.Combine(Directory.GetCurrentDirectory(), $"{device}_{record.WfLot}_{record.LotId}_temp.xlsx");
            string finalPath = Path.Combine(lotPath, $"{device}_{record.WfLot}_{record.CpNo}_#1-25.xlsx");
            
            
            // 加载ini 配置
            var iniConfig = IniConfigReader.GetDeviceConfig(record.Device);
            if (iniConfig == null)
            {
                LogHelper.Info($"未找到该device配置:{record.Device}");
                return;
            }
            
            int TMB_head = iniConfig.TmbHead;
            int TMB_start = iniConfig.TmbStart;



            #region 读取head，detail，split数据
            string headSql = @$"SELECT * FROM RTM_ADMIN.RTM_P_DATA_HEAD WHERE LOT_ID = '{record.LotId}' AND CP_NO = '{record.CpNo}' AND RP_NO = '{record.RpNo}'";

            DataTable headTable = OracleHelper.ExecuteQuery(headSql);

            string serialNo = headTable.Rows[0]["SERIAL_NO"].ToString()!;

            string detailSql = @$"SELECT
    			SERIAL_NO,
				WF_NO,
				WF_ID,
				HBIN,
				START_DATE,
				END_DATE,
				LOAD_DATE,
				UNLOAD_DATE,
				PASS_QTY,
				YIELD,
				TESTER,
				CARD_ID 
			FROM
				P_DATA_DETAIL 
			WHERE
				SERIAL_NO = :SERIAL_NO
			ORDER BY
				TO_NUMBER( WF_NO )";

            DataTable detailTable = OracleHelper.ExecuteQuery(detailSql, new OracleParameter(":SERIAL_NO", serialNo));


            // 读取程序名
            string splitSql = $@"SELECT TEST_P FROM RTM_ADMIN.RTM_P_DATA_SPLIT WHERE SERIAL_NO = :SERIAL_NO";
            string testP = OracleHelper.ExecuteScalar(splitSql, new OracleParameter(":SERIAL_NO", serialNo))?.ToString() ?? "";

            #endregion

            // 开始写入excel 文件
            using var workbook = new XLWorkbook(templatePath);
            var sheet = workbook.Worksheet("Bin_Summary");

            sheet.Cell("C1").Value = record.Device;
            sheet.Cell("F2").Value = testP;
            sheet.Cell("F3").Value = record.WfLot;

            foreach (DataRow row in detailTable.Rows)
            {
                string wfNo = row["WF_NO"].ToString()!;
                string hbin = row["HBIN"].ToString()!;
                string tester = row["TESTER"].ToString()!;
                string cardId = row["CARD_ID"].ToString()!;
                string wfId = row["WF_ID"].ToString()!;
                string startDate = row["START_DATE"].ToString()!;
                string endDate = row["END_DATE"].ToString()!;
                string loadDate = row["LOAD_DATE"].ToString()!;
                string unloadDate = row["UNLOAD_DATE"].ToString()!;
                string yield = row["YIELD"].ToString()!;
                
                int rowIndex = int.Parse(wfNo) + 6;

                // 清空行
                foreach (var cell in sheet.Row(rowIndex).Cells(8, 27))
                {
                    cell.Value = 0;
                }

                // 写入 Tester/CardID
                sheet.Cell("I2").Value = tester;
                sheet.Cell("K2").Value = cardId;
                sheet.Cell("K3").Value = startDate;


                sheet.Cell(rowIndex, 1).Value = startDate;
                sheet.Cell(rowIndex, 2).Value = testP;
                sheet.Cell(rowIndex, 3).Value = tester;
                sheet.Cell(rowIndex, 4).Value = cardId;
                sheet.Cell(rowIndex, 5).Value = record.WfLot;
                sheet.Cell(rowIndex, 6).Value = int.Parse(wfNo);
                double hhh = double.Parse(yield.Replace("%", "")) / 100;
                sheet.Cell(rowIndex, 7).Value = hhh;

                // 解析 hbin 并写入
                string[] binItems = hbin.Trim('|').Split('|');
                foreach (var item in binItems)
                {
                    
                    var parts = item.Split(':');
                    if (parts.Length != 2) continue;

                    string binCode = parts[0].Trim().PadLeft(2, '0'); // e.g. C01
                    int value = int.Parse(parts[1].Trim());

                    for (int col = 8; col <= 27; col++)
                    {
                        var header = sheet.Cell(6, col).GetString();
                        
                        if (header == $"BIN{binCode}")
                        {
                            sheet.Cell(rowIndex, col).Value = value;
                            break;
                        }
                    }
                }
                
                string tmxPath = Path.Combine(tmbFileBasePath, custCode, device, record.WfLot, record.CpNo, record.RpNo);
                string tmbFile = Path.Combine(tmxPath, $"{wfId}.tmb");

                if (!File.Exists(tmbFile) )
                {
                    LogHelper.Error($"tmb 文件:{tmbFile}");
                    continue;
                }
                
                List<List<string>> valueMap = ParseHelper.ParseTmbFile(tmbFile, TMB_head);



                var waferSheet = workbook.Worksheet(wfNo);
                
                waferSheet.Cell("C4").Value = device;
                waferSheet.Cell("C5").Value = record.WfLot;
                waferSheet.Cell("C6").Value = wfId;
                waferSheet.Cell("C7").Value = "8 INCH";
                waferSheet.Cell("C8").Value = "D";
                waferSheet.Cell("C9").Value = startDate;
                waferSheet.Cell("C10").Value = endDate;
                waferSheet.Cell("C11").Value = loadDate;
                waferSheet.Cell("C12").Value = unloadDate;
                
                
                for (int i = 0; i < valueMap.Count; i++)
                {
                    for (int j = 0; j < valueMap[i].Count; j++)
                    {
                        
                        string val = valueMap[i][j];
                        if (int.TryParse(val, out int result))
                            waferSheet.Cell(i + TMB_start + 1, j + 6).Value = result;
                        
                    }
                }

                waferSheet.Name = wfId;


            }

            // 删除多余sheet
            // 要删除的 sheet 名称集合
            var toDelete = Enumerable.Range(1, 26) // 0 到 25
                .Select(i => i.ToString())
                .ToHashSet();

            // 遍历并删除
            foreach (var sheetName in toDelete)
            {
                if (workbook.Worksheets.Contains(sheetName))
                {
                    workbook.Worksheets.Delete(sheetName);
                }
            }


            // 清除空行
            for (int i = 31; i >= 7; i--)
            {
                if (sheet.Cell(i, 1).IsEmpty())
                    sheet.Row(i).Delete();
            }

            workbook.RecalculateAllFormulas();
            workbook.CalculateMode = XLCalculateMode.Auto;
            workbook.SaveAs(tempPath);



            if (File.Exists(finalPath)) File.Delete(finalPath);
            workbook.SaveAs(finalPath);


            File.Delete(tempPath);

        }



    }
}
